USE Lib
GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
----------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'FN' AND name = 'fn_word_count') BEGIN
	DROP  FUNCTION  tsql.fn_word_count
	PRINT '<<< DROP FUNCTION tsql.fn_word_count - Completed with SUCCESS >>>'
END
GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/*================================================================================================
Name:			tsql.fn_word_count
Type:			User defined function 
Owner:					
Description:	Returns the number of words in a string, implements MsWord word count functionality 
Parameters:		@text = list of values comma delimited
				@advanced = specifies if it detects end of phrase, semicolon and other word sepparators.
Dependens on:  	fn_split
Usage:			SELECT [tsql].[fn_word_count] ('John is a nice person',0)
--------------------------------------------------------------------------------------------------
Project:		http://sqllib.codeplex.com/
Help:			http://sqllib.codeplex.com/wikipage?title=fn_word_count&referringTitle=Documentation		
Version:		20120920
SrcCtrlApp:		SVN
SrcCtrlPath:	http://sqllib.codeplex.com/SourceControl/list/changesets
--------------------------------------------------------------------------------------------------
History:		09/20/2012 - Adrian E Dudau - Function created
================================================================================================*/

CREATE FUNCTION [tsql].[fn_word_count] (@text NVARCHAR(MAX),@advanced BIT = 0)
RETURNS BIGINT
AS

BEGIN
	DECLARE @n BIGINT = 1;
	DECLARE @buffer NVARCHAR(MAX) = '';

	SET @buffer = REPLACE(@text,SPACE(1),CHAR(254))
	SET @buffer = tsql.fn_smart_replace(@buffer,CHAR(10), CHAR(254),1,1000)
	SET @buffer = tsql.fn_smart_replace(@buffer,CHAR(13), CHAR(254),1,1000)
	SET @buffer = tsql.fn_smart_replace(@buffer,CHAR(9), CHAR(254),1,1000)
	SET @buffer = tsql.fn_smart_replace(@buffer,'?', CHAR(254),1,1000)
	SET @buffer = tsql.fn_smart_replace(@buffer,'!', CHAR(254),1,1000)

	-- compensate for punctuation characters and other special characters  
	IF ISNULL(@advanced,0) = 1 BEGIN
		SET @buffer = tsql.fn_smart_replace(@buffer,'(', CHAR(254),1,1000)
		SET @buffer = tsql.fn_smart_replace(@buffer,'[', CHAR(254),1,1000)
		SET @buffer = tsql.fn_smart_replace(@buffer,'{', CHAR(254),1,1000)
		SET @buffer = tsql.fn_smart_replace(@buffer,';', CHAR(254),1,1000)

		SET @n = PATINDEX('%[,.][a-z]%',@buffer)
		WHILE @n > 0 BEGIN
			SET @buffer = LEFT(@buffer,@n-1) + CHAR(254) + SUBSTRING(@buffer,@n+1,LEN(@buffer))
			SET @n = PATINDEX('%[,.][a-z]%',@buffer);
		END 		
	END
	
	SET @buffer = tsql.fn_smart_replace(@buffer,CHAR(254), CHAR(254),1,1000)

	RETURN (SELECT COUNT(item_value) FROM tsql.fn_split(@buffer, CHAR(254))) 
END

GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
-- Verify completion
----------------------------
IF EXISTS(SELECT * FROM sysobjects WHERE type = 'FN' AND name = 'fn_word_count') BEGIN 
	PRINT '<<< CREATE FUNCTION tsql.fn_word_count - Completed with SUCCESS >>>'
END

ELSE BEGIN 
	PRINT '<<< CREATE FUNCTION tsql.fn_word_count - Completed with ERROR >>>'
END

GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------


